﻿using Microsoft.AspNetCore.Http;
using Middleware.Extenssion;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Middleware
{
    public class DBHelper
    {
        private string ConnectString { get; }
        private SqlConnection connection;
        public DBHelper(ConfigSection section)
        {
            ConnectString = section.ConnectionString;
        }

        private void init()
        {
            if (connection == null)
            {
                connection = new SqlConnection(ConnectString);
            }
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            if (connection.State == ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
        }
        /// <summary>
        /// 查询方法
        /// 任意类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> GetEntities<T>(string sql)
        {
            init();
            SqlDataAdapter ad = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds.Tables[0].GetEntity<T>();
        }
        /// <summary>
        /// 查询方法
        /// 只能是BaseEntity的派生类
        /// </summary>
        /// <typeparam name="T">实体类，属性名必须与查询出来的列名一样</typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> GetList<T>(string sql) where T : BaseEntity
        {
            sql = sql.ToLower();
            init();
            if(sql.Contains("where") )
            {
                if (sql.Contains("group by") || sql.Contains("order by "))
                {
                    sql = sql.Insert(sql.IndexOf("where")+5, " and isdelete=0 ");
                }
                else
                {
                    sql += " and isdelete=0";
                }

            } //过滤已删除的数据
            else
            {
                if (sql.Contains("group by") )
                {
                  sql=  sql.Insert(sql.IndexOf("group by") -1, " where isdelete=0 ");
                }
                else if(!sql.Contains("group by")&&sql.Contains("order by"))
                {
                    sql = sql.Insert(sql.IndexOf("order by") - 1, " where isdelete=0 ");
                }
                else
                {
                    sql += " where isdelete=0";
                }
            }
            SqlDataAdapter ad = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds.Tables[0].GetEntity<T>();
        }
        /// <summary>
        /// 执行增加、修改、删除
        /// </summary>
        /// <typeparam name="T">数据库对象，必须继承于BaseEntity</typeparam>
        /// <param name="t">要操作的对象</param>
        /// <param name="operate">操作方法：insert,update,delete</param>
        /// <returns></returns>
        public bool ExcuteEntity<T>(T t, SqlOperate operate,HttpContext context) where T : BaseEntity
        {
            init();
            SqlCommand cmd;
            int uid = context.Items.GetUser().UserId;
            
            bool res;
            switch (operate)
            {
                case SqlOperate.Insert:
                    t.CreateId = uid;
                    cmd = new SqlCommand(t.GetInsertSql(), connection); break;
                case SqlOperate.Update:
                    t.ModifyId = uid;
                    t.ModifyTime = DateTime.Now;
                    cmd = new SqlCommand(t.GetUpdateSql(), connection); break;
                case SqlOperate.Delete:
                    t.ModifyId = uid;
                    t.ModifyTime = DateTime.Now;
                    cmd = new SqlCommand(t.GetDeleteSql(), connection); break;
                default:
                    return false;
            }
            res = cmd.ExecuteNonQuery() > 0;
            connection.Close();
            return res;
        }
    }
}
